package com.sushe.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentQueryDao {

    private static final String url="jdbc:mysql://localhost:3306/sushe";
    private static final String username="root";
    private static final String password="123456";

    public  List<String> queryStudentNameByRoomId(Integer roomId) throws ClassNotFoundException, SQLException {
        List<String> list=new ArrayList<>();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select s.name\n" +
                "from room r join bed b on r.id=b.room_id join student s on s.id=b.student_id\n" +
                "where r.id=?";
        Connection connection= DriverManager.getConnection(url, username, password);
        PreparedStatement stmt=connection.prepareStatement(sql);
        stmt.setInt(1,roomId);
        ResultSet rs=stmt.executeQuery();
        while(rs.next()) {
            list.add(rs.getString("name"));
        }
        return list;
    }
}
